﻿Imports System
Imports System.Data
Imports System.Data.OleDb
Imports QuanLyNhaSachDTO
Public Class SachDAO
#Region "Duyệt danh sách"
    Public Shared Function DaTatableToListof(ByVal Table As DataTable) As List(Of Sach)
        Dim arr As New List(Of Sach)
        For Each row As DataRow In Table.Rows
            Dim book As New Sach()
            book.IDSach = row("IDSach")
            book.DonGia = row("DonGia")
            book.NgaySanXuat = row("NgaySanXuat")
            book.SoLuong = row("SoLuong")
            book.TacGia = row("TacGia")
            book.TenSach = row("TenSach")
            book.TheLoaiSach = row("TheLoaiSach")
            'book.TrangThai = row("TrangThai")
            arr.Add(book)
        Next
        Return arr
    End Function

    Public Function LayDanhSach() As List(Of Sach)
        Dim arr As New List(Of Sach)
        Dim da As New DataTable()
        Dim cn As OleDbConnection = Dataprovider.connetion()
        Dim sql As String = "select * form Sach"
        Dim cmd As New OleDbCommand(sql, cn)
        Dim adr As New OleDbDataAdapter()
        adr.SelectCommand = cmd
        adr.Fill(da)
        arr = DaTatableToListof(da)
        cn.Close()
        Return arr
    End Function

    Public Function LaySachTheoTheLoai(ByVal TheLoaiSach As String, ByVal timchinhxac As Boolean) As List(Of Sach)
        Dim arr As New List(Of Sach)
        Dim da As New DataTable()
        Dim cn As OleDbConnection = Dataprovider.connetion()
        Dim sql As String
        If (timchinhxac) Then
            sql = "select * form Sach where TheLoaiSach = ?"
        Else
            sql = "select * form Sach where TheLoaiSach like %?%"
        End If
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@TheLoaiSach", OleDbType.WChar)
        cmd.Parameters("@TheLoaiSach").Value = TheLoaiSach
        Dim adr As New OleDbDataAdapter()
        adr.SelectCommand = cmd
        arr = DaTatableToListof(da)
        cn.Close()
        Return arr
    End Function
#End Region

#Region "Update Danh Sách"

    Public Function ThemSach(ByVal book As Sach) As Exception
        Dim ex As Exception
        Dim ma As Integer = 0
        Dim cn As OleDbConnection = Dataprovider.connetion()
        Dim sql As String = "insert into Sach(TenSach,TheLoaiSach,TacGia,SoLuong,DonGia,NgaySanXuat) values (?,?,?,?,?,?)"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@TenSach", OleDbType.WChar)
        cmd.Parameters.Add("@TheLoaiSach", OleDbType.WChar)
        cmd.Parameters.Add("@TacGia", OleDbType.WChar)
        cmd.Parameters.Add("@SoLuong", OleDbType.Integer)
        cmd.Parameters.Add("@DonGia", OleDbType.Double)
        cmd.Parameters.Add("@NgaySanXuat", OleDbType.Date)

        cmd.Parameters("@TenSach").Value = book.TenSach
        cmd.Parameters("@TheLoaiSach").Value = book.TheLoaiSach
        cmd.Parameters("@TacGia").Value = book.TacGia
        cmd.Parameters("@SoLuong").Value = book.SoLuong
        cmd.Parameters("@DonGia").Value = book.DonGia
        cmd.Parameters("@NgaySanXuat").Value = book.NgaySanXuat

        cmd.ExecuteNonQuery()
        Dim resql As String = "select @@IDENTITY"
        Dim recmd As New OleDbCommand(resql, cn)
        book.IDSach = cmd.ExecuteScalar()
        If book.IDSach <> 0 Then
            ex = New Exception("Thêm Sách" & book.TenSach & " Thành Công (IDSach: " & book.IDSach & ")")
        Else
            ex = New Exception("Thêm Sách" & book.TenSach & " Không Thành Công (IDSach: <Không Có> )")
        End If
        cn.Close()
        Return ex
    End Function

    Public Function XoaSach(ByVal book As Sach) As Exception
        Dim ex As New Exception("")
        Dim thongkesach As New ThongKeSachDAO()
        thongkesach.XoaThongKeSachTheoID(book.IDSach)
        Dim cn As OleDbConnection = Dataprovider.connetion()
        Dim sql As String = "Delete from Sach where IDSach = ?"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@IDSach", OleDbType.Integer)
        cmd.Parameters("@IDSach").Value = book.IDSach
        Dim err As Integer = cmd.ExecuteNonQuery()
        If err <> 0 Then
            ex = New Exception("Sách" & book.TenSach & " Được Xóa Thành Công (IDSach: " & book.IDSach & ")")
        Else
            ex = New Exception("Sách" & book.TenSach & " Được Xóa Thất Bại (IDSach: " & book.IDSach & ")")
        End If
        Return ex
    End Function

    Public Function XoaSach(ByVal books As List(Of Sach)) As List(Of Exception)
        Dim arrex As New List(Of Exception)
        For Each book As Sach In books
            Dim ex As Exception = XoaSach(book)
            arrex.Add(ex)
        Next
        Return arrex
    End Function

#End Region
End Class
